package com.msc.mc.util.data;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.compress.archivers.ArchiveOutputStream;
import org.apache.commons.compress.archivers.zip.ZipArchiveEntry;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.msc.mc.util.ImportBean;
import com.msc.mc.util.ObjectBean;
import com.msc.mc.util.ObjectListBean;
import com.msc.mc.util.SimManagerImportBean;

public class MatImport2 {
	public static void main( String[] args ) throws Exception {
		String path = "/data/mc_workspaces/SDMWeb/src/Material Name and ID.xlsx";
		MatImport2 matImport = new MatImport2( path );
		//matImport.generateImportStructXml( "/data/"  );
		matImport.parseImportFile();
		System.out.println( matImport.importData.toJSONString() );
	}
	
	private static final String SUFFIX_2003 = ".xls";
    private static final String SUFFIX_2007 = ".xlsx";

    private static FormulaEvaluator evaluator;
    
    private String importFilePath ;
    
    private JSONArray importData;
    
    private JSONObject matClassification;
    
    
    public void setMatClassification(JSONObject matClassification) {
		this.matClassification = matClassification;
	}


	public MatImport2( String mFilePath ) {
    	importFilePath = mFilePath ;
    }
    
    
    private void parseImportFile()throws Exception {
    	Workbook wb = initWorkBook( importFilePath );
    	importData = new JSONArray();
    	org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0);
    	int rowLen = sheet.getPhysicalNumberOfRows();
    	for( int r = 2 ; r < rowLen ; r++ ) {
    		Row row = sheet.getRow( r );
    		if(  row.getCell( 0 ) == null &&  row.getCell( 1 ) == null &&  row.getCell( 2 ) == null ) {
    			break;
    		}
    	
    		JSONObject jsonObj = new JSONObject();
    		//PLM UID
			jsonObj.put( "plmUid" , getPlmUid( row  ) );
			//Material Name
        	jsonObj.put( "name" , getMatName( row ) );
        	jsonObj.put( "matSymbol" , getMatSymbol( row ) );
        	jsonObj.put( "specSuffix" , getSpecSuffix( row ) );
        	//Material Id
        	jsonObj.put( "matId" , getMatId( row ) );
        	//color
        	jsonObj.put( "pmlColor" , getPlmColor( row ) );
        	//density
    		jsonObj.put( "plmDensity" , getPlmDensity( row ) );
    		jsonObj.put( "density" , getDensity( row ) );
    		jsonObj.put( "elastic" , getElastic( row ) );
    		jsonObj.put( "poissonRatio" , getPoissonRatio( row ) );
    		//classification
        	jsonObj.put( "classification" , getClassificationPath( row ) ) ;
        	//specification
        	jsonObj.put( "specId" , getSpecId( row ) );
        	jsonObj.put( "spec" , getSpec( row ) );
        	importData.add( jsonObj );
    	}
    }
    
    //--------------------------------------------------------------------------
    private long timeSeq = 0 ;
    public String generateImportStructXml( String workDir   ) throws Exception {
    	
    	if( importData == null ) {
    		parseImportFile();
    	}
    	
    	SimManagerImportBean importBean = new SimManagerImportBean();
		importBean.addParam("canEditDuringImport", "false");
		importBean.addParam("useOidDuringImport", "false");
		
		ObjectListBean matSchemal = importBean.addObjectList( "MatMaterialsSchema" , "[name == 'NIOMaterial']", true );
		
		ObjectListBean matSpecBean  = importBean.addObjectList( "MatPropertyDefReference" , "[name == 'matSpec']", true );
		ObjectListBean matClassBean = importBean.addObjectList( "MatPropertyDefReference" , "[name == 'matClassification']", true );
		ObjectListBean densityProp  = importBean.addObjectList( "MatPropertyDefMeasure" , "[name == 'density']", true );
		ObjectListBean tensileModulusProp  = importBean.addObjectList( "MatPropertyDefMeasure" , "[name == 'tensileModulus']", true );
		ObjectListBean poissonsRatioProp  = importBean.addObjectList( "MatPropertyDefMeasure" , "[name == 'poissonsRatio']", true );
		
		
		//---------------------------------
		ObjectBean dsItem = importBean.addObject( "Item" );
		dsItem.addAttributeBean( "name" , getUniqueName( "DS" ) , "trueAndAutoAdjust" );
		
		ObjectBean dsVariant = importBean.addObject( "Variant" );
		dsVariant.addAttributeBean( "item" , dsItem.getId() , "false" );
		dsVariant.addAttributeBean( "name" , "V1" , "false" );
		
		ObjectBean dsBean = importBean.addObject( "MatDataSource" );
		dsBean.addAttributeBean( "name", getUniqueName( "DS" ) , "false" ); 
		dsBean.addAttributeBean( "schema", matSchemal.getId() , "false" );
		dsBean.addAttributeBean( "variant", dsVariant.getId() , "false" );
		//---------------------------------
		
		for( int i = 0 ; i < importData.size() ; i++ ) {
			JSONObject jsonObj = importData.getJSONObject( i );
			String matName =  jsonObj.getString( "name" );
			
			
			ObjectBean designDataItem = importBean.addObject( "Item" );
			designDataItem.addAttributeBean( "name" , getUniqueName( matName )  ,  "trueAndAutoAdjust" );
			
			ObjectBean designDataVariant = importBean.addObject( "Variant" );
			designDataVariant.addAttributeBean( "item" , designDataItem.getId() ,  "false" );
			designDataVariant.addAttributeBean( "name" , "V1",  "false" );
			
			List< ImportBean > refBeans = new ArrayList< ImportBean >();
			
			String density = jsonObj.getString( "density" );
			if( StringUtils.isNotBlank( density ) ) {
				ObjectBean propDataBean = importBean.addObject( "MatMeasureProperty" );
				propDataBean.addMeasureBean( "value" , "Nominal" , density , "1"  , "Density" , "g_per_cm__3" );
				propDataBean.addAttributeBean( "matPropertyDef", densityProp , "false" );
				propDataBean.addAttributeBean( "name", "density" , "false" );
				propDataBean.addAttributeBean( "variant", designDataVariant , "false" );
				refBeans.add( propDataBean );
			}
			
			String elastic = jsonObj.getString( "elastic" );
			if( StringUtils.isNotBlank( elastic ) ) {
				ObjectBean propDataBean = importBean.addObject( "MatMeasureProperty" );
				propDataBean.addMeasureBean( "value" , "Nominal" , elastic , "1"  , "Modulus" , "MPa" );
				propDataBean.addAttributeBean( "matPropertyDef", tensileModulusProp , "false" );
				propDataBean.addAttributeBean( "name", "density" , "false" );
				propDataBean.addAttributeBean( "variant", designDataVariant , "false" );
				refBeans.add( propDataBean );
			}
			
			String poissonRatio = jsonObj.getString( "poissonRatio" );
			if( StringUtils.isNotBlank( density ) ) {
				ObjectBean propDataBean = importBean.addObject( "MatMeasureProperty" );
				propDataBean.addMeasureBean( "value" , "Nominal" , poissonRatio , "1"  , "Dimensionless" , "nodimension" );
				propDataBean.addAttributeBean( "matPropertyDef", poissonsRatioProp , "false" );
				propDataBean.addAttributeBean( "name", "density" , "false" );
				propDataBean.addAttributeBean( "variant", designDataVariant , "false" );
				refBeans.add( propDataBean );
			}
			
			
			String specCondition = null;
			String specId = jsonObj.getString( "specId" );
			String spec = jsonObj.getString( "spec" );
			if( StringUtils.isNotBlank( specId ) ) {
				specCondition = "[plmUid=='" + specId + "']" ;
			}else if( StringUtils.isNotBlank( spec ) ) {
				specCondition = "[specCode=='" + spec + "'].activeRevision()";
			}
			
			if( StringUtils.isNotBlank( specCondition ) ) {
				ObjectListBean olB = importBean.addObjectList( "NIOMatSpecification"  , specCondition , true );
				//jsonObj.put( "spec_ref" , olB.getId() );
				ObjectBean specBean = importBean.addObject( "MatReferenceProperty" );
				specBean.addAttributeBean( "matPropertyDef", matSpecBean , "false" );
				specBean.addAttributeBean( "name", "matSpec" , "false" );
				specBean.addAttributeBean( "value", olB , "false" );
				specBean.addAttributeBean( "variant", designDataVariant , "false" );
				refBeans.add( specBean );
			}
		
			
			String classification = jsonObj.getString( "classification" );
			
			JSONObject classJsonObj = matClassification.getJSONObject( classification );
//System.out.println( classification );
			String classOid = classJsonObj.getString( "oid" );
			
			ObjectListBean olBClass  = importBean.addObjectList( "NIOMatClassification" , "[objectId=='" + classOid + "']", true );
			//jsonObj.put( "classification_ref" , olB.getId() );
		
			ObjectBean classBean = importBean.addObject( "MatReferenceProperty" );
			classBean.addAttributeBean( "matPropertyDef", matClassBean , "false" );
			classBean.addAttributeBean( "name", "matClassification" , "false" );
			classBean.addAttributeBean( "value", olBClass , "false" );
			classBean.addAttributeBean( "variant", designDataVariant , "false" );
			refBeans.add( classBean );
			
			
			
//			ObjectBean requestBean = importBean.addObject( "MatReferenceProperty" );
//			requestBean.addAttributeBean( "matPropertyDef", matRequest , "false" );
//			requestBean.addAttributeBean( "name", "newMaterialRequest" , "false" );
//			requestBean.addAttributeBean( "value", matReq , "false" );
//			requestBean.addAttributeBean( "variant", designDataVariant , "false" );
			
			ObjectBean designDataBean = importBean.addObject( "NIOMaterial" );
			designDataBean.addAttributeBean("name", matName , "false" );
			designDataBean.addAttributeBean("matSpecNumber",spec, "false" );
			//designDataBean.addAttributeBean("materialSymbol", materialSymbol , "false" );
			designDataBean.addAttributeBean("description","New Material", "false" );
			designDataBean.addAttributeBean("variant" , designDataVariant.getId() , "false" );
			designDataBean.addAttributeBean("matRevisableProperties", refBeans.toArray( new ImportBean[refBeans.size()]  )  , "false" );//TODO
			designDataBean.addAttributeBean("generatedFromModel",  dsBean   , "false" );
			
			designDataBean.addAttributeBean("materialGroup", classJsonObj.getString( "group" ) , "false" );
			designDataBean.addAttributeBean("materialSubgroup",classJsonObj.getString( "subgroup" ), "false" );
			if( StringUtils.isNotBlank(  classJsonObj.getString( "category" ) ) ) {
				designDataBean.addAttributeBean("materialCategory",classJsonObj.getString( "category" ), "false" );
			}
			
			if( StringUtils.isNotBlank( jsonObj.getString( "matSymbol" ) ) ) {
				designDataBean.addAttributeBean("materialSymbol",jsonObj.getString( "matSymbol" ), "false" );
			}
			if( StringUtils.isNotBlank( jsonObj.getString( "specSuffix" ) ) ) {
				designDataBean.addAttributeBean("specSuffix",jsonObj.getString( "specSuffix" ), "false" );
			}
			
			String matId = jsonObj.getString( "matId" );
			if( StringUtils.isNotBlank( matId ) ) {
				designDataBean.addAttributeBean( "matId" , matId, "false" );
				//designDataBean.addAttributeBean( "caeId" , "0001" , "false" );
			}
			
			String plmDensity = jsonObj.getString( "plmDensity" );
			if( plmDensity != null ) {
				designDataBean.addMeasureBean( "plm_density" , "Nominal" , plmDensity , "2", "Density", "kg_per_m__3" );
			}
			String color = jsonObj.getString( "plmColor" );
			if( StringUtils.isNotBlank( color ) ) {
				designDataBean.addAttributeBean( "plm_color" ,  color   , "false" );
			}
		}
		
		//String message = importBean.getXMLString();
		File xmlFile   =  importBean.getXMLFile( workDir , "structure.xml"  );
		
		String zipFilePath = workDir + "MaterialEditor.zip" ;
		FileOutputStream fos=new java.io.FileOutputStream(zipFilePath); 
		ArchiveOutputStream zaos = new org.apache.commons.compress.archivers.ArchiveStreamFactory().createArchiveOutputStream("zip",new java.io.FileOutputStream(zipFilePath) );
		 
		ZipArchiveEntry zipArchiveEntry = new org.apache.commons.compress.archivers.zip.ZipArchiveEntry( xmlFile.getName() );    
		//添加成员到压缩包对象中    
		zaos.putArchiveEntry(zipArchiveEntry);     
		//获取当前文件的输入流    
		FileInputStream fis = new java.io.FileInputStream( xmlFile );
		//将输入流内容读取到压缩包输出流中    
		org.apache.commons.compress.utils.IOUtils.copy( fis , zaos);     
		//清除entry对象准备添加下一个文件   
		zaos.closeArchiveEntry();    
		fis.close(); 
		zaos.finish();
		fos.close();
		return zipFilePath ;
    }
    
    public  String getUniqueName( String prefix ) {
		//return prefix + CoreWidgetsUtils.generateUnqiueWidgetName();
		return prefix + Long.toHexString( timeSeq++ ) ;
	}
    
    
    private String getPlmUid( Row row ) {
    	Cell cell = row.getCell( 0 );
    	String matId = null ;
    	if( cell != null ) {
    		cell.setCellType( 1 );
    		matId =  cell.getStringCellValue();
    	}
    	return matId;
    }
    
    private String getMatName( Row row ) {
		String name1 = null  , name2 = null;
    	Cell cell1 = row.getCell( 1 );
    	if( cell1 != null ) {
    		cell1.setCellType( 1 );
    		name1 = cell1.getStringCellValue() ;
    	}
    	
		Cell cell2 = row.getCell( 2 );
		if( cell2 != null ) {
			cell2.setCellType( 1 );
			name2 = cell2.getStringCellValue() ;
		}
		return StringUtils.isBlank( name1 ) ? name2 : name1 ;
    }
    
    private String getMatSymbol( Row row ) {
    	Cell cell = row.getCell( 3 );
    	String res = null ;
    	if( cell != null ) {
    		res =  cell.getStringCellValue();
    	}
    	return res;
    }
    
    private String getSpecSuffix( Row row ) {
    	Cell cell = row.getCell( 4 );
    	String res = null ;
    	if( cell != null ) {
    		res =  getCellValueByCell( cell );
    	}
    	return res;
    }
    
    private String getMatId( Row row ) {
    	Cell cell = row.getCell( 5 );
    	String matId = null ;
    	if( cell != null ) {
    		matId =  cell.getStringCellValue();
    	}
    	return matId;
    }
    
    private String getPlmColor( Row row ) {
    	Cell cell = row.getCell( 6 );
    	String color = null ;
    	if( cell != null ) {
	    	 color =  cell.getStringCellValue();
    	}
    	return color;
    }
    
    private double getPlmDensity( Row row ) {
    	Cell cell = row.getCell( 9 );
    	String str =  cell.getStringCellValue();
    	double density = 0 ;
    	if( str.endsWith( "KG__M3" ) ) {
    		density = Double.parseDouble( str.substring( 0 , str.length() -  6 ) ) ;
    	}
    	return density;
    }
    
    private String getDensity( Row row ) {
    	Cell cell = row.getCell( 10 );
    	String density =  null ;
    	if( cell != null ) {
	    	String str =  cell.getStringCellValue();
	    	if( str.endsWith( "KG__M3" ) ) {
	    		density =   str.substring( 0 , str.length() -  6 )   ;
	    	}
	    }
    	return density;
    }
    
    private String getElastic( Row row ) {
    	Cell cell = row.getCell( 11 );
    	String elastic = null;
    	if( cell != null ) {
    		elastic = getCellValueByCell( cell ) ;
    	}
    	return elastic;
    }
    
    private String getPoissonRatio( Row row ) {
    	Cell cell = row.getCell( 12 );
    	String elastic = null;
    	if( cell != null ) {
    		elastic = getCellValueByCell( cell ) ;
    	}
    	return elastic;
    }
     
    
    private String getClassificationPath( Row row ) {
    	Cell cell = row.getCell( 15 );
    	String classification = null ;
    	if( cell != null ) {
    		classification =  cell.getStringCellValue();
    	}
    	return classification;
    }
    

    private String getSpecId( Row row ) {
    	Cell cell = row.getCell( 16 );
    	String res = null ;
    	if( cell != null ) {
    		res =  cell.getStringCellValue();
    	}
    	return res;
    }
    
    
    private String getSpec( Row row ) {
    	Cell cell = row.getCell( 17 );
    	String res = null ;
    	if( cell != null ) {
    		res =  cell.getStringCellValue();
    	}
    	return res;
    }
    
    
    
    //获取单元格各类型值，返回字符串类型
    private  String getCellValueByCell(Cell cell) {
        //判断是否为null或空串
        if (cell==null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        int cellType=cell.getCellType();
        if(cellType==Cell.CELL_TYPE_FORMULA){ //表达式类型
            cellType=evaluator.evaluate(cell).getCellType();
        }
         
        switch (cellType) {
        case Cell.CELL_TYPE_STRING: //字符串类型
            cellValue= cell.getStringCellValue().trim();
            cellValue=StringUtils.isEmpty(cellValue) ? "" : cellValue; 
            break;
        case Cell.CELL_TYPE_BOOLEAN:  //布尔类型
            cellValue = String.valueOf(cell.getBooleanCellValue()); 
            break; 
        case Cell.CELL_TYPE_NUMERIC: //数值类型
             if (HSSFDateUtil.isCellDateFormatted(cell)) {  //判断日期类型
            	 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                 cellValue = sdf.format( cell.getDateCellValue() );
             } else {  //否
                 cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue()); 
             } 
            break;
        default: //其它类型，取空串吧
            cellValue = "";
            break;
        }
        return cellValue;
    }
    
    /**
    * 初始化
    * @return
    * @throws IOException
    */
   public  Workbook initWorkBook( String mFilePath ) throws IOException {
        File file = new File(mFilePath);
        InputStream is = new FileInputStream(file);

        Workbook workbook = null;
        //根据后缀，得到不同的Workbook子类，即HSSFWorkbook或XSSFWorkbook
        if (mFilePath.endsWith(SUFFIX_2003)) {
            workbook = new HSSFWorkbook(is);
        } else if (mFilePath.endsWith(SUFFIX_2007)) {
            workbook = new XSSFWorkbook(is);
        }
        
        evaluator= workbook.getCreationHelper().createFormulaEvaluator();
        
        return workbook;
    }
  
	
}
